今天在公司遇到一个有趣的sql问题,觉得挺有趣的,那就分享下。
问题
有如下数据,找出其中日期(rq字段)连续性大于等于3的日期。
即,结果为如下图所示:
思考
最开始的时候,我想这至少得写存储过程吧,先排序这个表,然后再从前向后取,当遇到连续性大于2的就保留下来,以此类推,直到读取完毕。
后来,百度了下,发现了一个更好的方法—-“关于数字的经典SQL编程:连续范围问题”。
思路
在那篇文章中,题主用的是整形,我们可以类推下,整形和时间其实差不多。
第一步,查找每行的与rownum的差值
在这里我们用系统时间来确定相差的天数,保证了每个时间都有唯一的对标,然后减去rownum。
select rq, floor(rq - sysdate) - rownum as diff ,rownum from tmptable
执行结果如下图所示,我们发现,只要时间是连续的,那么他们的差值(diff字段)一定相等。
第二步,根据分组查找大于等于3的差值
这个简单,使用havaing搞定,贴sql就行了,不多说了。
select diff
from (select floor(rq - sysdate) - rownum as diff from tmptable t) aa
having count(*) > 2
group by diff
第三步,根据差值查找出所有的行
差值都有了,这就好办啦,我们直接使用第一步的sql和第二步的sql进行关联查询,结果就出来啦。
select t.rq
from
-- 查找每行的与rownum的差值
(select rq, floor(rq - sysdate) - rownum as diff from tmptable) t,
-- 根据分组查找大于等于3的差值
(select diff
from (select floor(rq - sysdate) - rownum as diff from tmptable t) aa
having count(*) > 2
group by diff) cg
where t.diff = cg.diff
order by t.rq ;
好了,结果就是最开始要求的数据。
总结
在连续性这个问题上,只要我们找到了与连续性相关的字段(rownum),然后在这个字段上进行处理,就可以得到我们想要的结果了。
从这里我们可以看到,如果需要做某件事的时候,先找是否有与它相关联的东西,如果有,那么我们可以先从相关联的的东西上入手,然后再用死方法一步一步来。